use master
go

if exists(select * from sys.databases where name = 'tbl')
drop database tbl

create database tbl
go

use tbl
go

create table tbl_card
(
	id varchar(10) primary key,
	passWord varchar(10) not null,
	balance decimal(10,1) not null,
	userName nchar(2) not null
)

create table tbl_computer
(
	id varchar(10) primary key,
	onUse char(1) check(onUse='0' or  onUse='1') not null,
	note text
)

create table tbl_record
(
	id int primary key,
	cardId varchar(10) references tbl_card(id) not null,
	ComputerId varchar(10) references tbl_computer(id) not null,
	beginTime datetime not null,
	endTime datetime not null,
	fee decimal(10,1) 
)

insert into tbl_card(id,passWord,balance,userName ) values
('0023_ABC','555','98','张军'),
('0025_bbd','abe','300','朱俊'),
('0036_CCD','何柳','100','何柳'),
('0045_YGR','0045_YGR','58','证验'),
('0078_RJV','55885fg','600','校庆'),
('0089_EDE','zhang','134','张峻')


insert into tbl_computer(id,onUse,note) values
('02',0,'25555'),
('03',1,'55555'),
('04',0,'66666'),
('05',1,'8888'),
('06',0,'688878'),
('B01',0,'558558')

insert into tbl_record(id,cardId,ComputerId,beginTime,endTime,fee) values
('23','0078_RJV','B01','2007-07-15 19:00:00','2007-07-15 21:00:00','20'),
('34','0025_bbd','02','2006-12-25 18:00:00','2006-12-25 22:00:00','23'),
('45','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
('46','0023_ABC','03','2006-12-22 15:26:00','2006-12-22 22:55:00','6'),
('47','0023_ABC','03','2006-12-23 15:26:00','2006-12-23 22:55:00','50'),
('48','0023_ABC','03','2007-01-06 15:26:00','2007-01-06 22:55:00','6'),
('55','0023_ABC','03','2006-07-21 15:26:00','2006-07-21 22:55:00','50'),
('64','0045_YGR','04','2006-12-24 18:00:00','2006-12-24 22:00:00','30'),
('65','0025_bbd','02','2006-12-28 18:00:00','2006-12-28 22:00:00','23'),
('98','0025_bbd','02','2006-12-26 18:00:00','2006-12-26 22:00:00','23')
--请完成以下题目：
--1. 查询出用户名为'张军'的上网卡的上网记录，要求显示卡号，用户名，机器编号、开始时间、结束时间，和消费金额，并按消费金额降序排列
select tc.id 卡号,userName 用户名,tc.id 机器编号,beginTime 开始时间,endTime 结束时间,fee 消费金额 from tbl_card tc
inner join tbl_record tr on tc.id = tr.cardId where userName='张军' order by fee desc

--2. 查询出每台机器上的上网次数和消费的总金额
select ComputerId,count(*) 上网次数,sum(fee)消费的总金额 from tbl_record group by ComputerId

--3. 查询出所有已经使用过的上网卡的消费总金额
select cardId ,sum(fee)消费总金额 from tbl_record group by cardId

--4. 查询出从未消费过的上网卡的卡号和用户名
select tc.id,tc.userName from tbl_card tc 
left join tbl_record tr on tr.cardId = tc.id where tr.id is null

--5. 将密码与用户名一样的上网卡信息查询出来
select * from tbl_card t1 join tbl_card t2 on t1.id = t2.id and t1.passWord = t2.userName

--6. 查询出使用次数最多的机器号和使用次数
select ComputerId,count(*)使用次数 from tbl_record group by ComputerId
having count(*)=(select max(m.使用次数) from (select ComputerId,count(*)使用次数 from tbl_record group by ComputerId)m)

--7. 查询出卡号是以'ABC'结尾的卡号，用户名，上网的机器号和消费金额
select tc.id 卡号,tc.userName 用户名,tr.ComputerId 机器号,tr.fee 消费金额 from tbl_record tr join  tbl_card tc on tr.cardId = tc.id

where tc.id like '%ABC'
--8. 查询出是周六、周天上网的记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select tc.id 卡号,tc.userName 用户名,tr.ComputerId 机器号,beginTime 开始时间,endTime 结束时间,tr.fee 消费金额 from tbl_record tr join  tbl_card tc on tr.cardId = tc.id
where datename(DW,beginTime)='星期六' or datename(DW,beginTime)='星期日'

--9. 查询出一次上网时间超过12小时的的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select tc.id 卡号,tc.userName 用户名,tr.ComputerId 机器号,beginTime 开始时间,endTime 结束时间,tr.fee 消费金额 from tbl_record tr join  tbl_card tc on tr.cardId = tc.id
where datediff(HH,beginTime,endTime)>12

--10. 查询出消费金额排列前三名(最高)的上网记录，要求显示上网的卡号，用户名，机器号，开始时间、结束时间和消费金额
select tc.id 卡号,tc.userName 用户名,tr.ComputerId 机器号,beginTime 开始时间,endTime 结束时间, tr.fee 消费金额 from tbl_record tr join  tbl_card tc on tr.cardId = tc.id
where tr.fee in (select distinct top 3 fee from tbl_record order by fee desc) order by fee desc


